package com.sxjh.service.impl;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.date.LocalDateTimeUtil;
import cn.hutool.core.io.IoUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.poi.excel.BigExcelWriter;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import com.sxjh.config.AuthContent;
import com.sxjh.entity.TrainBookedEntity;
import com.sxjh.entity.param.*;
import com.sxjh.entity.result.*;
import com.sxjh.service.*;
import com.sxjh.service.scalereport.ScaleReportService;
import org.apache.poi.ss.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;

import javax.annotation.Resource;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.time.LocalDate;
import java.util.*;


@Service
public class ExcelServiceImpl implements ExcelService {

  @Autowired
  private FinanceService financeService;

  @Autowired
  private ScaleReportService scaleReportService;

  @Autowired
  private TrainBookedService trainBookedService;

  @Autowired
  private TemporaryTransportService temporaryTransportService;

  @Resource
  private UnloadLogService unloadLogService;

  @Override
  public BigExcelWriter createExcel(Integer exportType, String paramJson) {
    return null;
  }

  // 甲醇Excel数据处理
  @Override
  public void jcExcel(Finance8Param param, HttpServletResponse response) {
    String title = "甲醇厂-甲醇财务报表";
    // 获取源数据
    param.setMaterialId(17);
    List<Finance8Result> list = financeService.list21(param);
    // 最终保存到Excel的数据
    List<List> data = new ArrayList<>();
    // 创建Excel
    ExcelWriter excelWriter = ExcelUtil.getWriter();
    // 设置标题
    List<String> titles = CollUtil.newArrayList(
            "序号", "公司名称", "物料名称", "数量（吨）", "单价",
            "物料总价", "运费", "总价", "税率（%）", "物料总价",
            "运费");
    excelWriter.merge(titles.size() - 1, title);
    data.add(new ArrayList()); // 添加一个空行便于后续存放开始日期，结束日期
    data.add(new ArrayList()); // 添加一个空号便于标题存放
    data.add(titles);
    // 处理Excel数据
    for (Finance8Result item : list) {
      data.add(CollUtil.newArrayList(
              item.getSerialNumber(), item.getCompanyName(), item.getMaterialName(),
              item.getWeighbridgeWeight(), item.getSettlementPrice(),
              item.getSettlementPriceSum(), item.getFreightSum(), item.getMoneySum(),
              item.getTaxRate(), item.getSettlementPriceSumNo(), item.getFreightSumNo()));
    }
    data.add(new ArrayList()); // 空行存放制表人，制表时间
    // 写入数据
    excelWriter.write(data);
    // 设置行高
    excelWriter.setRowHeight(0,40);
    // 设置列宽（列宽与行高单位不一样）
    excelWriter.setColumnWidth(0, 5);
    excelWriter.setColumnWidth(1, 40);
    excelWriter.setColumnWidth(2, 10);
    excelWriter.setColumnWidth(3, 10);
    excelWriter.setColumnWidth(5, 15);
    excelWriter.setColumnWidth(7, 12);
    excelWriter.setColumnWidth(9, 15);
    excelWriter.setColumnWidth(10, 12);
    // 框线实线全黑样式
    CellStyle border = excelWriter.createCellStyle();
    border.setBorderLeft(BorderStyle.THIN);
    border.setBorderRight(BorderStyle.THIN);
    border.setBorderTop(BorderStyle.THIN);
    border.setBorderBottom(BorderStyle.THIN);
    // 合并一些单元格
    excelWriter.merge(1, 1, 0, 1, "开始日期："
            + LocalDateTimeUtil.format(param.getStartDate(), "yyyy-MM-dd HH:mm:ss"), border);
    excelWriter.merge(1, 1, 2, 10, "结束日期："
            + LocalDateTimeUtil.format(param.getEndDate(), "yyyy-MM-dd HH:mm:ss"), border);
    // 标题样式
    CellStyle cellStyle = excelWriter.createCellStyle();
    cellStyle.setAlignment(HorizontalAlignment.CENTER);
    cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
    cellStyle.setBorderRight(BorderStyle.THIN);
    cellStyle.setBorderTop(BorderStyle.THIN);
    cellStyle.setBorderLeft(BorderStyle.THIN);
    // 合并标题
    excelWriter.merge(2, 3, 0, 0, titles.get(0), cellStyle);
    excelWriter.merge(2, 3, 1, 1, titles.get(1), cellStyle);
    excelWriter.merge(2, 3, 2, 2, titles.get(2), cellStyle);
    excelWriter.merge(2, 3, 3, 3, titles.get(3), cellStyle);
    excelWriter.merge(2, 2, 4, 6, "含税", cellStyle);
    excelWriter.merge(2, 3, 7, 7, titles.get(7), cellStyle);
    excelWriter.merge(2, 3, 8, 8, titles.get(8), cellStyle);
    excelWriter.merge(2, 2, 9, 10, "不含税", cellStyle);
    // 制表人制表时间
    String userName = AuthContent.getUser() != null ? AuthContent.getUser().getName() : "";
    excelWriter.merge(data.size(), data.size(), 0, 1, "制表人：" + userName, border);
    excelWriter.merge(data.size(), data.size(), 2, 10, "制表日期：" + LocalDate.now(), border);
    // 保留四位小数的样式
    CellStyle cellStyle1 = excelWriter.createCellStyle();
    cellStyle1.setDataFormat(excelWriter.getWorkbook().createDataFormat().getFormat("0.0000"));
    cellStyle1.setBorderLeft(BorderStyle.THIN);
    cellStyle1.setBorderRight(BorderStyle.THIN);
    cellStyle1.setBorderTop(BorderStyle.THIN);
    cellStyle1.setBorderBottom(BorderStyle.THIN);
    // 同样的公司名称合并
    String company = list.size() > 0 ? list.get(0).getCompanyName() : "";
    int start = 0; // 起始位置
    for (int i = 0; i < list.size(); i++) {
      // 遇到下一个公司名称时，合并之前相同的公司
      if (!list.get(i).getCompanyName().equals(company)) {
        // 合并单元格
        if (i - start > 1) {
          excelWriter.merge(4 + start, 4 + i - 1, 1, 1, company, null);
        }
        start = i;
        company = list.get(i).getCompanyName();
      }
      // 不含税等字段保留四位小数
      excelWriter.setStyle(cellStyle1, 9, i + 4);
      excelWriter.setStyle(cellStyle1, 10, i + 4);
    }
    // 导出Excel
    exportExcel(title, excelWriter, response);
  }

//  public static void main(String[] args) {
//    BigDecimal a =new BigDecimal("0.00");
//    BigDecimal b =new BigDecimal("2.22");
//    for (int i = 0; i < 5; i++) {
//      a=a.add(b);
//    }
//    System.out.println("aplus b is : " + a);
//  }

  @Override
  public void jcMxExcel(Finance8List4Param param, HttpServletResponse response) {
    String title = "甲醇厂-甲醇明细财务报表";
    // 获取源数据
    List<Finance8List4Result> list = financeService.list25(param);
    // 最终保存到Excel的数据
    List<List> data = new ArrayList<>();
    // 创建Excel
    ExcelWriter excelWriter = ExcelUtil.getWriter();
    // 设置标题
    List<String> titles = CollUtil.newArrayList(
            "序号", "公司名称", "车牌号", "物料名称", "数量（吨）", "单价",
            "物料总价", "运费", "总价", "税率（%）", "物料总价",
            "运费");
    excelWriter.merge(titles.size() - 1, title);
    data.add(new ArrayList()); // 添加一个空行便于后续存放开始日期，结束日期
    data.add(new ArrayList()); // 添加一个空号便于标题存放
    data.add(titles);
    // 处理Excel数据
    for (Finance8List4Result item : list) {
      data.add(CollUtil.newArrayList(
              item.getSerialNumber(), item.getCompanyName(),item.getPlateNumber(), item.getMaterialName(),
              item.getWeighbridgeWeight(), item.getSettlementPrice(),
              item.getSettlementPriceSum(), item.getFreightSum(), item.getMoneySum()
//              ,item.getTaxRate(), item.getSettlementPriceSumNo(), item.getFreightSumNo()));
      ));
    }
    data.add(new ArrayList()); // 空行存放制表人，制表时间
    // 写入数据
    excelWriter.write(data);
    // 设置行高
    excelWriter.setRowHeight(0,40);
    // 设置列宽（列宽与行高单位不一样）
    excelWriter.setColumnWidth(0, 5);
    excelWriter.setColumnWidth(1, 40);
    excelWriter.setColumnWidth(2, 10);
    excelWriter.setColumnWidth(3, 10);
    excelWriter.setColumnWidth(4, 10);
    excelWriter.setColumnWidth(6, 15);
    excelWriter.setColumnWidth(8, 12);
    excelWriter.setColumnWidth(10, 15);
    excelWriter.setColumnWidth(11, 12);
    // 框线实线全黑样式
    CellStyle border = excelWriter.createCellStyle();
    border.setBorderLeft(BorderStyle.THIN);
    border.setBorderRight(BorderStyle.THIN);
    border.setBorderTop(BorderStyle.THIN);
    border.setBorderBottom(BorderStyle.THIN);
    // 合并一些单元格
    excelWriter.merge(1, 1, 0, 1, "开始日期："
            + LocalDateTimeUtil.format(param.getStartDate(), "yyyy-MM-dd HH:mm:ss"), border);
    excelWriter.merge(1, 1, 2, 11, "结束日期："
            + LocalDateTimeUtil.format(param.getEndDate(), "yyyy-MM-dd HH:mm:ss"), border);
    // 标题样式
    CellStyle cellStyle = excelWriter.createCellStyle();
    cellStyle.setAlignment(HorizontalAlignment.CENTER);
    cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
    cellStyle.setBorderRight(BorderStyle.THIN);
    cellStyle.setBorderTop(BorderStyle.THIN);
    cellStyle.setBorderLeft(BorderStyle.THIN);
    // 合并标题
    excelWriter.merge(2, 3, 0, 0, titles.get(0), cellStyle);
    excelWriter.merge(2, 3, 1, 1, titles.get(1), cellStyle);
    excelWriter.merge(2, 3, 2, 2, titles.get(2), cellStyle);
    excelWriter.merge(2, 3, 3, 3, titles.get(3), cellStyle);
    excelWriter.merge(2, 3, 4, 4, titles.get(4), cellStyle);
    excelWriter.merge(2, 2, 5, 7, "含税", cellStyle);
    excelWriter.merge(2, 3, 8, 8, titles.get(7), cellStyle);
    excelWriter.merge(2, 3, 9, 9, titles.get(8), cellStyle);
    excelWriter.merge(2, 2, 10, 11, "不含税", cellStyle);
    // 制表人制表时间
    String userName = AuthContent.getUser() != null ? AuthContent.getUser().getName() : "";
    excelWriter.merge(data.size(), data.size(), 0, 1, "制表人：" + userName, border);
    excelWriter.merge(data.size(), data.size(), 2, 11, "制表日期：" + LocalDate.now(), border);
    // 保留四位小数的样式
    CellStyle cellStyle1 = excelWriter.createCellStyle();
    cellStyle1.setDataFormat(excelWriter.getWorkbook().createDataFormat().getFormat("0.0000"));
    cellStyle1.setBorderBottom(BorderStyle.THIN);
    cellStyle1.setBorderTop(BorderStyle.THIN);
    cellStyle1.setBorderRight(BorderStyle.THIN);
    cellStyle1.setBorderLeft(BorderStyle.THIN);
    // 同样的公司名称合并
    String company = list.size() > 0 ? list.get(0).getCompanyName() : "";
    int start = 0; // 起始位置
    for (int i = 0; i < list.size(); i++) {
      // 遇到下一个公司名称时，合并之前相同的公司
      if (!list.get(i).getCompanyName().equals(company)) {
        // 合并单元格
        if (i - start > 1) {
          excelWriter.merge(4 + start, 4 + i - 1, 1, 1, company, null);
        }
        start = i;
        company = list.get(i).getCompanyName();
      }
      // 不含税等字段保留四位小数
      excelWriter.setStyle(cellStyle1, 10, i + 4);
      excelWriter.setStyle(cellStyle1, 11, i + 4);
    }
    // 导出Excel
    exportExcel(title, excelWriter, response);
  }

  @Override
  public void temporaryTransportExcel(TemporaryTransportParam param, HttpServletResponse response) {
    String title = "临时运输报表Excel";
    // 获取源数据
    List<TemporaryTransportResult> list = temporaryTransportService.selectTemporaryTransport(param);
    // 最终保存到Excel的数据
    List<List> data = new ArrayList<>();
    // 创建Excel
    ExcelWriter excelWriter = ExcelUtil.getWriter();
    // 设置标题
    List<String> titles = CollUtil.newArrayList(
            "序号", "派车单号", "客户/供应商", "车牌号码", "物料名称", "物流承运公司",
            "出入大门", "毛重", "毛重时间", "皮重", "皮重时间","净重", "称重单号", "司机姓名", "联系电话",
            "进厂时间", "进厂地点", "出厂时间", "出厂地点");
    excelWriter.merge(titles.size() - 1, title);
    data.add(new ArrayList()); // 添加一个空行便于后续存放开始日期，结束日期
    data.add(new ArrayList()); // 添加一个空号便于标题存放
    data.add(titles);
    int index = 1;
    int carNumber = list.size();  //总车数
    BigDecimal roughWeight = new BigDecimal(0.00); //皮重（吨）
    BigDecimal tareWeight = new BigDecimal(0.00);   //毛重（吨）
    BigDecimal netWeight = new BigDecimal(0.00);    //净重（吨
    // 处理Excel数据
    for (TemporaryTransportResult item : list) {
        if(!StringUtils.isEmpty(item.getGrossWeight())){
          tareWeight = tareWeight.add(item.getGrossWeight());
        }
        if(!StringUtils.isEmpty(item.getTareWeight())){
          roughWeight = roughWeight.add(item.getTareWeight());
        }
        if(!StringUtils.isEmpty(item.getNetWeight())){
          netWeight = netWeight.add(item.getNetWeight());
        }
        data.add(CollUtil.newArrayList(
                index, item.getDispatchNum(),item.getCompanyName(), item.getVehicleNum(),item.getTransportMaterials(), item.getTransportUnit()
                ,item.getPlace(),item.getGrossWeight(),item.getGrossWeightTime(),item.getTareWeight(),item.getTareWeightTime(),item.getNetWeight(),
                item.getWeightNo(), item.getName(),item.getPhone(), item.getEnterDatetime(),item.getEnterDoor(),item.getOutDatetime(),
                item.getOutDoor()
        ));
        index++;
      }
      data.add(CollUtil.newArrayList(
              "合计", "","","总车数："+carNumber,"", ""
              ,"",tareWeight+"吨","",roughWeight+"吨","",netWeight+"吨","",
              "","","", "","",""
      ));

      data.add(new ArrayList()); // 空行存放制表人，制表时间
      // 写入数据
      excelWriter.write(data);
      // 设置行高
      excelWriter.setRowHeight(0,40);
      // 设置列宽（列宽与行高单位不一样）
      excelWriter.setColumnWidth(0, 5);
      excelWriter.setColumnWidth(1, 25);
      excelWriter.setColumnWidth(2, 25);
      excelWriter.setColumnWidth(3, 15);
      excelWriter.setColumnWidth(4, 30);
      excelWriter.setColumnWidth(5, 30);
      excelWriter.setColumnWidth(6, 30);
      excelWriter.setColumnWidth(8, 15);
      excelWriter.setColumnWidth(10, 15);
      excelWriter.setColumnWidth(11, 15);
      excelWriter.setColumnWidth(12, 15);
      excelWriter.setColumnWidth(13, 15);
      excelWriter.setColumnWidth(14, 25);
      excelWriter.setColumnWidth(15, 25);
      excelWriter.setColumnWidth(16, 25);
      excelWriter.setColumnWidth(17, 25);
      excelWriter.setColumnWidth(18, 25);

//    excelWriter.setColumnWidth(16, 25);
      // 框线实线全黑样式
      CellStyle border = excelWriter.createCellStyle();
      border.setBorderLeft(BorderStyle.THIN);
      border.setBorderRight(BorderStyle.THIN);
      border.setBorderTop(BorderStyle.THIN);
      border.setBorderBottom(BorderStyle.THIN);
      // 合并一些单元格
      excelWriter.merge(1, 1, 0, 1, "进场日期："
              + param.getEnterDatetime(), border);
      excelWriter.merge(1, 1, 2, 11, "出厂日期："
              + param.getOutDatetime(), border);
      // 标题样式
      CellStyle cellStyle = excelWriter.createCellStyle();
      cellStyle.setAlignment(HorizontalAlignment.CENTER);
      cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
      cellStyle.setBorderRight(BorderStyle.THIN);
      cellStyle.setBorderTop(BorderStyle.THIN);
      cellStyle.setBorderLeft(BorderStyle.THIN);
      // 合并标题
//    excelWriter.merge(2, 3, 0, 0, titles.get(0), cellStyle);
//    excelWriter.merge(2, 3, 1, 1, titles.get(1), cellStyle);
//    excelWriter.merge(2, 3, 2, 2, titles.get(2), cellStyle);
//    excelWriter.merge(2, 3, 3, 3, titles.get(3), cellStyle);
//    excelWriter.merge(2, 3, 4, 4, titles.get(4), cellStyle);
//    excelWriter.merge(2, 2, 5, 7, "含税", cellStyle);
//    excelWriter.merge(2, 3, 8, 8, titles.get(7), cellStyle);
//    excelWriter.merge(2, 3, 9, 9, titles.get(8), cellStyle);
//    excelWriter.merge(2, 2, 10, 11, "不含税", cellStyle);
      // 制表人制表时间
      String userName = AuthContent.getUser() != null ? AuthContent.getUser().getName() : "";
      excelWriter.merge(data.size(), data.size(), 0, 1, "制表人：" + userName, border);
      excelWriter.merge(data.size(), data.size(), 2, 11, "制表日期：" + LocalDate.now(), border);
      // 保留四位小数的样式
      CellStyle cellStyle1 = excelWriter.createCellStyle();
      cellStyle1.setDataFormat(excelWriter.getWorkbook().createDataFormat().getFormat("0.0000"));
      cellStyle1.setBorderBottom(BorderStyle.THIN);
      cellStyle1.setBorderTop(BorderStyle.THIN);
      cellStyle1.setBorderRight(BorderStyle.THIN);
      cellStyle1.setBorderLeft(BorderStyle.THIN);
//    // 同样的公司名称合并
//    String company = list.size() > 0 ? list.get(0).getCompanyName() : "";
//    int start = 0; // 起始位置
//    for (int i = 0; i < list.size(); i++) {
//      // 遇到下一个公司名称时，合并之前相同的公司
//      if (!list.get(i).getCompanyName().equals(company)) {
//        // 合并单元格
//        if (i - start > 1) {
//          excelWriter.merge(4 + start, 4 + i - 1, 1, 1, company, null);
//        }
//        start = i;
//        company = list.get(i).getCompanyName();
//      }
//      // 不含税等字段保留四位小数
//      excelWriter.setStyle(cellStyle1, 10, i + 4);
//      excelWriter.setStyle(cellStyle1, 11, i + 4);
//    }
      // 导出Excel
      exportExcel(title, excelWriter, response);



  }

  @Override
  public void reportBdFyExcel(ScaleReportParam param, HttpServletResponse response) {
    String title = "磅单报表-"+param.getCompanyName();//companyName用来临时存放标题的 ps:不想再加字段。。。
    // 获取源数据
    param.setCompanyName(null);
    List<ScaleReportResult> list = scaleReportService.getScaleReportByPageAll(param);
    this.reportExcel(param, response, list, title);
  }

  @Override
  public void reportExcel(ScaleReportParam param, HttpServletResponse response) {
    String title = "磅单报表-"+param.getCompanyName();//companyName用来临时存放标题的 ps:不想再加字段。。。
    // 获取源数据
    param.setCompanyName(null);
    List<ScaleReportResult> list = scaleReportService.getScaleReport(param).getMyList();
    this.reportExcel(param, response, list, title);
  }

  private void reportExcel(ScaleReportParam param, HttpServletResponse response
          , List<ScaleReportResult> list, String title) {
    // 最终保存到Excel的数据
    List<List> data = new ArrayList<>();
    // 创建Excel
    ExcelWriter excelWriter = ExcelUtil.getWriter();
    // 设置标题
    List<String> titles = CollUtil.newArrayList(
            "序号", "入厂日期时间", "出厂日期时间", "车牌号", "客户名称", "物料名称","仓库名称", "仓库人员",
            "毛重", "皮重",
            "净重", "结算量","原发数", "水分", "水分扣重", "初称时间",
            "回称时间");
    excelWriter.merge(titles.size() - 1, title);
    data.add(new ArrayList()); // 添加一个空行便于后续存放开始日期，结束日期
    data.add(new ArrayList()); // 添加一个空号便于标题存放
    data.add(titles);
    int index = 1;
    //统计总计行
    int leaveFactory = 0; //已出厂车辆
    int notFactory = 0;   //未出厂车辆
    int carNumber = list.size();  //总车数
    BigDecimal roughWeight = new BigDecimal(0.00); //皮重（吨）
    BigDecimal tareWeight = new BigDecimal(0.00);   //毛重（吨）
    BigDecimal netWeight = new BigDecimal(0.00);    //净重（吨）
    BigDecimal settlement = new BigDecimal(0.00);   //结算量（吨）
    // 处理Excel数据
    for (ScaleReportResult item : list) {
      if(null == item.getFactoryOutTime()){
        notFactory++;
      }
      if(null != item.getmWeight()){
        roughWeight = roughWeight.add(item.getmWeight());
      }
      if(null != item.getpWeight()){
        tareWeight = tareWeight.add(item.getpWeight());
      }
      if(null != item.getWeighbridgeWeight()){
        netWeight = netWeight.add(item.getWeighbridgeWeight());
      }
      if(null != item.getOriginalWeight()){
        settlement = settlement.add(item.getOriginalWeight());
      }
      data.add(CollUtil.newArrayList(
              index, item.getFactoryInTime(),item.getFactoryOutTime(), item.getCarNumber(),item.getCompanyName(), item.getProductName(),item.getWarehouseName(), item.getWuserName(),
              item.getmWeight(),item.getpWeight(),
              item.getWeighbridgeWeight(),item.getOriginalWeight(),item.getNetWeight(), item.getMoisture(),item.getMoistureDeduction(), item.getCdatetime(),item.getHdatetime()
      ));
      index++;
    }
    leaveFactory = list.size() - notFactory;
    data.add(CollUtil.newArrayList(
            "合计", "已出厂："+leaveFactory,"未出厂："+notFactory,"总车数："+carNumber,"", "","", "",
            roughWeight+"吨",tareWeight+"吨",
            netWeight+"吨",settlement+"吨",
            "","","", "",""
    ));


    data.add(new ArrayList()); // 空行存放制表人，制表时间
    // 写入数据
    excelWriter.write(data);
    // 设置行高
    excelWriter.setRowHeight(0,40);
    // 设置列宽（列宽与行高单位不一样）
    excelWriter.setColumnWidth(0, 5);
    excelWriter.setColumnWidth(1, 25);
    excelWriter.setColumnWidth(2, 25);
    excelWriter.setColumnWidth(3, 15);
    excelWriter.setColumnWidth(4, 30);
    excelWriter.setColumnWidth(5, 30);
    excelWriter.setColumnWidth(6, 30);
    excelWriter.setColumnWidth(8, 15);
    excelWriter.setColumnWidth(9, 15);
    excelWriter.setColumnWidth(10, 15);
    excelWriter.setColumnWidth(11, 15);
    excelWriter.setColumnWidth(12, 15);
    excelWriter.setColumnWidth(13, 15);
    excelWriter.setColumnWidth(15, 25);
    excelWriter.setColumnWidth(16, 25);
    // 框线实线全黑样式
    CellStyle border = excelWriter.createCellStyle();
    border.setBorderLeft(BorderStyle.THIN);
    border.setBorderRight(BorderStyle.THIN);
    border.setBorderTop(BorderStyle.THIN);
    border.setBorderBottom(BorderStyle.THIN);
    // 合并一些单元格
    excelWriter.merge(1, 1, 0, 1, "开始日期："
            + LocalDateTimeUtil.format(param.getStartDate(), "yyyy-MM-dd HH:mm:ss"), border);
    excelWriter.merge(1, 1, 2, 11, "结束日期："
            + LocalDateTimeUtil.format(param.getEndDate(), "yyyy-MM-dd HH:mm:ss"), border);
    // 标题样式
    CellStyle cellStyle = excelWriter.createCellStyle();
    cellStyle.setAlignment(HorizontalAlignment.CENTER);
    cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
    cellStyle.setBorderRight(BorderStyle.THIN);
    cellStyle.setBorderTop(BorderStyle.THIN);
    cellStyle.setBorderLeft(BorderStyle.THIN);
    // 合并标题
//    excelWriter.merge(2, 3, 0, 0, titles.get(0), cellStyle);
//    excelWriter.merge(2, 3, 1, 1, titles.get(1), cellStyle);
//    excelWriter.merge(2, 3, 2, 2, titles.get(2), cellStyle);
//    excelWriter.merge(2, 3, 3, 3, titles.get(3), cellStyle);
//    excelWriter.merge(2, 3, 4, 4, titles.get(4), cellStyle);
//    excelWriter.merge(2, 2, 5, 7, "含税", cellStyle);
//    excelWriter.merge(2, 3, 8, 8, titles.get(7), cellStyle);
//    excelWriter.merge(2, 3, 9, 9, titles.get(8), cellStyle);
//    excelWriter.merge(2, 2, 10, 11, "不含税", cellStyle);
    // 制表人制表时间
    String userName = AuthContent.getUser() != null ? AuthContent.getUser().getName() : "";
    excelWriter.merge(data.size(), data.size(), 0, 1, "制表人：" + userName, border);
    excelWriter.merge(data.size(), data.size(), 2, 11, "制表日期：" + LocalDate.now(), border);
    // 保留四位小数的样式
    CellStyle cellStyle1 = excelWriter.createCellStyle();
    cellStyle1.setDataFormat(excelWriter.getWorkbook().createDataFormat().getFormat("0.0000"));
    cellStyle1.setBorderBottom(BorderStyle.THIN);
    cellStyle1.setBorderTop(BorderStyle.THIN);
    cellStyle1.setBorderRight(BorderStyle.THIN);
    cellStyle1.setBorderLeft(BorderStyle.THIN);
//    // 同样的公司名称合并
//    String company = list.size() > 0 ? list.get(0).getCompanyName() : "";
//    int start = 0; // 起始位置
//    for (int i = 0; i < list.size(); i++) {
//      // 遇到下一个公司名称时，合并之前相同的公司
//      if (!list.get(i).getCompanyName().equals(company)) {
//        // 合并单元格
//        if (i - start > 1) {
//          excelWriter.merge(4 + start, 4 + i - 1, 1, 1, company, null);
//        }
//        start = i;
//        company = list.get(i).getCompanyName();
//      }
//      // 不含税等字段保留四位小数
//      excelWriter.setStyle(cellStyle1, 10, i + 4);
//      excelWriter.setStyle(cellStyle1, 11, i + 4);
//    }
    // 导出Excel
    exportExcel(title, excelWriter, response);
  }

  @Override
  public void unloadDetailsExcel(UnloadLogListParam param, HttpServletResponse response) {
    String title = "内部倒运明细报表Excel";
    // 获取源数据
    List<UnloadLogListResult> list = unloadLogService.details(param);
    // 最终保存到Excel的数据
    List<List> data = new ArrayList<>();
    // 创建Excel
    ExcelWriter excelWriter = ExcelUtil.getWriter();
    // 设置标题
    List<String> titles = CollUtil.newArrayList(
            "序号", "车牌号", "地磅单编号", "物料名称", "装货点", "卸货点",
            "卸货时间", "毛重/吨", "皮重/吨","净重/吨");
    excelWriter.merge(titles.size() - 1, title);
//    data.add(new ArrayList()); // 添加一个空行便于后续存放开始日期，结束日期
//    data.add(new ArrayList()); // 添加一个空号便于标题存放
    data.add(titles);
    int index = 1;
    int carNumber = list.size();  //总车数
    BigDecimal roughWeight = new BigDecimal(0.00); //毛重（吨）
    BigDecimal tareWeight = new BigDecimal(0.00);   //皮重（吨）
    BigDecimal netWeight = new BigDecimal(0.00);    //净重（吨
    // 处理Excel数据
    for (UnloadLogListResult item : list) {
      if(StrUtil.isNotBlank(item.getTare())){
        tareWeight = tareWeight.add(new BigDecimal(item.getTare()));
      }
      if(StrUtil.isNotBlank(item.getRough())){
        roughWeight = roughWeight.add(new BigDecimal(item.getRough()));
      }
      if(StrUtil.isNotBlank(item.getNetWeight())){
        netWeight = netWeight.add(new BigDecimal(item.getNetWeight()));
      }
      data.add(CollUtil.newArrayList(
              index, item.getVehNumber(),item.getNumber(), item.getMaterialName(),item.getLoadName()
              , item.getUnloadName(),item.getCreatedAt(),item.getRough(),item.getTare(),item.getNetWeight()
      ));
      index++;
    }
    data.add(CollUtil.newArrayList(
            "合计","总车数："+carNumber,"","", "", "",""
            ,roughWeight+"吨",tareWeight+"吨",netWeight+"吨"
    ));

    data.add(new ArrayList()); // 空行存放制表人，制表时间
    // 写入数据
    excelWriter.write(data);
    // 设置行高
    excelWriter.setRowHeight(0,40);
    // 设置列宽（列宽与行高单位不一样）
    excelWriter.setColumnWidth(0, 5);
    excelWriter.setColumnWidth(1, 25);
    excelWriter.setColumnWidth(2, 25);
    excelWriter.setColumnWidth(3, 15);
    excelWriter.setColumnWidth(4, 30);
    excelWriter.setColumnWidth(5, 30);
    excelWriter.setColumnWidth(6, 25);
    excelWriter.setColumnWidth(7, 15);
    excelWriter.setColumnWidth(8, 15);
    excelWriter.setColumnWidth(9, 15);

//    excelWriter.setColumnWidth(16, 25);
    // 框线实线全黑样式
    CellStyle border = excelWriter.createCellStyle();
    border.setBorderLeft(BorderStyle.THIN);
    border.setBorderRight(BorderStyle.THIN);
    border.setBorderTop(BorderStyle.THIN);
    border.setBorderBottom(BorderStyle.THIN);
    // 合并一些单元格
//    excelWriter.merge(1, 1, 0, 1, "进场日期："
//            + param.getStartDate(), border);
//    excelWriter.merge(1, 1, 2, 11, "出厂日期："
//            + param.getEndDate(), border);
    // 标题样式
    CellStyle cellStyle = excelWriter.createCellStyle();
    cellStyle.setAlignment(HorizontalAlignment.CENTER);
    cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
    cellStyle.setBorderRight(BorderStyle.THIN);
    cellStyle.setBorderTop(BorderStyle.THIN);
    cellStyle.setBorderLeft(BorderStyle.THIN);
    // 制表人制表时间
    String userName = AuthContent.getUser() != null ? AuthContent.getUser().getName() : "";
    excelWriter.merge(data.size(), data.size(), 0, 1, "制表人：" + userName, border);
    excelWriter.merge(data.size(), data.size(), 2, 9, "制表日期：" + LocalDate.now(), border);
    // 保留四位小数的样式
    CellStyle cellStyle1 = excelWriter.createCellStyle();
    cellStyle1.setDataFormat(excelWriter.getWorkbook().createDataFormat().getFormat("0.0000"));
    cellStyle1.setBorderBottom(BorderStyle.THIN);
    cellStyle1.setBorderTop(BorderStyle.THIN);
    cellStyle1.setBorderRight(BorderStyle.THIN);
    cellStyle1.setBorderLeft(BorderStyle.THIN);
    // 导出Excel
    exportExcel(title, excelWriter, response);
  }

  @Override
  public Map<String,Object> importTrainBooked(List<TrainBookedEntity> entities,String belongsFactory) {
    Map<String,Object> resultMap = new HashMap<>();
    int succeedCount = 0;
    int errCount = 0;
    int index = 0;
    String errMsg = "";
    for (TrainBookedEntity entity : entities) {
      try {
        entity.setBelongsFactory(belongsFactory);
        trainBookedService.save(entity);
        succeedCount++;
      }catch (Exception e){
        errMsg += "第"+index+"条导入异常，请检查数据完整性！";
        errCount++;
        continue;
      }finally {
        index++;
      }
    }
    resultMap.put("succeedCount",succeedCount);
    resultMap.put("errCount",errCount);
    resultMap.put("errMsg",errMsg);
    return resultMap;
  }

  // 将处理好的Excel导出
  public void exportExcel(String title, ExcelWriter excelWriter, HttpServletResponse response) {
    response.setContentType("application/vnd.ms-excel;charset=utf-8");
    String contentDisposition = "";
    try {
      contentDisposition = "attachment;filename*=UTF-8''"+ URLEncoder.encode(title + ".xls", "UTF-8");
    } catch (UnsupportedEncodingException e) {
      e.printStackTrace();
    }
    response.setHeader("Content-Disposition",contentDisposition);
    ServletOutputStream out= null;
    try {
      out = response.getOutputStream();
    } catch (IOException e) {
      e.printStackTrace();
    }
    excelWriter.flush(out, true);
    // 关闭writer，释放内存
    excelWriter.close();
    //此处记得关闭输出Servlet流
    IoUtil.close(out);
  }





}
